﻿using MySql.Data.MySqlClient;
using System;
using System.Configuration;
using System.Data;
using System.Text.RegularExpressions;
using System.Threading;

namespace sqltomongo
{
    public static class MySqlHelper
    {
        private static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["mysql_conn"].ConnectionString;
        private static MySqlConnection mysqlConnected;
        public static int ExecuteNonQuery(string SQLString)
        {
            int result;
            using (MySqlConnection connection = new MySqlConnection(MySqlHelper.connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        result = rows;
                    }
                    catch (MySqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
            return result;
        }
        public static int ExecuteNonQuery(string SQLString, params MySqlParameter[] cmdParms)
        {
            int result;
            using (MySqlConnection connection = new MySqlConnection(MySqlHelper.connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    try
                    {
                        MySqlHelper.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        result = rows;
                    }
                    catch (MySqlException e)
                    {
                        throw e;
                    }
                }
            }
            return result;
        }
        public static object ExecuteScalar(string SQLString)
        {
            object result;
            using (MySqlConnection connection = new MySqlConnection(MySqlHelper.connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
                        {
                            result = null;
                        }
                        else
                        {
                            result = obj;
                        }
                    }
                    catch (MySqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
            return result;
        }
        public static object ExecuteScalar(string SQLString, params MySqlParameter[] cmdParms)
        {
            object result;
            using (MySqlConnection connection = new MySqlConnection(MySqlHelper.connectionString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    try
                    {
                        MySqlHelper.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
                        {
                            result = null;
                        }
                        else
                        {
                            result = obj;
                        }
                    }
                    catch (MySqlException e)
                    {
                        throw e;
                    }
                }
            }
            return result;
        }


        public static object ExecuteScalar(string sql, MySqlParameter[] param, bool isNewConnected)
        {
            if (isNewConnected)
                return ExecuteScalar(sql, param);

            try
            {
                if (mysqlConnected.State != ConnectionState.Open)
                {
                    CloseConnected();
                }
                if (!OpenConnected(connectionString))
                {
                    Thread.Sleep(3);

                    if (!OpenConnected(connectionString))
                    {
                        throw new Exception("数据库链接失败！");
                    }
                }



                return new object();
            }
            catch (Exception ex)
            {
                throw ex;
            }


            //return new object();
            //try 
            //{
            //    if (mysqlConnected.State == ConnectionState.Open) 
            //    {


            //    }

            //}
            //catch (Exception ex) 
            //{


            //}
        }


        public static bool OpenConnected(string strConnect)
        {
            try
            {
                mysqlConnected = new MySqlConnection();
                mysqlConnected.ConnectionString = strConnect;
                mysqlConnected.Open();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        private static bool CloseConnected()
        {
            try
            {
                if (mysqlConnected != null)
                {
                    mysqlConnected.Close();
                }
                return true;
            }
            catch (Exception ex)
            {
                try
                {
                    mysqlConnected.Close();
                }
                catch { }
            }
            return true;
        }


        public static MySqlDataReader ExecuteReader(string strSQL)
        {
            MySqlConnection connection = new MySqlConnection(MySqlHelper.connectionString);
            MySqlCommand cmd = new MySqlCommand(strSQL, connection);
            MySqlDataReader myReader = null;
            MySqlDataReader result;
            try
            {
                connection.Open();
                myReader = cmd.ExecuteReader();
                result = myReader;
            }
            catch (MySqlException e)
            {
                throw e;
            }
            finally
            {
                myReader.Close();
            }
            return result;
        }
        public static MySqlDataReader ExecuteReader(string SQLString, params MySqlParameter[] cmdParms)
        {
            MySqlConnection connection = new MySqlConnection(MySqlHelper.connectionString);
            MySqlCommand cmd = new MySqlCommand();
            MySqlDataReader myReader = null;
            MySqlDataReader result;
            try
            {
                MySqlHelper.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                myReader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                result = myReader;
            }
            catch (MySqlException e)
            {
                throw e;
            }
            finally
            {
                myReader.Close();
                cmd.Dispose();
                connection.Close();
            }
            return result;
        }
        public static DataTable ExecuteDataTable(string SQLString)
        {
            DataTable result;
            using (MySqlConnection connection = new MySqlConnection(MySqlHelper.connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                result = ds.Tables[0];
            }
            return result;
        }
        public static DataSet ExecuteDataSet(string SQLString)
        {
            DataSet result;
            using (MySqlConnection connection = new MySqlConnection(MySqlHelper.connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    MySqlCommand cmd = new MySqlCommand(SQLString, connection);
                    cmd.CommandTimeout = 9999999;
                    MySqlDataAdapter command = new MySqlDataAdapter(cmd);
                    command.Fill(ds, "ds");
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                result = ds;
            }
            return result;
        }
        public static DataTable ExecuteDataTable(string SQLString, bool isNewConnected)
        {
            if (isNewConnected)
                return ExecuteDataTable(SQLString);

            try
            {
                if (mysqlConnected == null)
                {
                    mysqlConnected = new MySqlConnection(connectionString);
                }
                if (mysqlConnected.State != ConnectionState.Open)
                {
                    CloseConnected();
                    if (!OpenConnected(connectionString))
                    {
                        Thread.Sleep(3);
                        if (!OpenConnected(connectionString))
                        {
                            throw new Exception("数据库链接失败！");
                        }
                    }
                }

                DataSet ds = new DataSet();
                MySqlDataAdapter command = new MySqlDataAdapter(SQLString, mysqlConnected);
                command.Fill(ds, "ds");
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public static DataTable ExecuteDataTable(string SQLString, params MySqlParameter[] cmdParms)
        {
            DataTable result;
            using (MySqlConnection connection = new MySqlConnection(MySqlHelper.connectionString))
            {
                MySqlCommand cmd = new MySqlCommand();
                MySqlHelper.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (MySqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    result = ds.Tables[0];
                }
            }
            return result;
        }
        public static DataTable ExecuteDataTable(string cmdText, int startResord, int maxRecord)
        {
            DataTable result;
            using (MySqlConnection connection = new MySqlConnection(MySqlHelper.connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    MySqlDataAdapter command = new MySqlDataAdapter(cmdText, connection);
                    command.Fill(ds, startResord, maxRecord, "ds");
                }
                catch (MySqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                result = ds.Tables[0];
            }
            return result;
        }
        public static DataTable getPager(out int recordCount, string selectList, string tableName, string whereStr, string orderExpression, int pageIdex, int pageSize)
        {
            int rows = 0;
            DataTable dt = new DataTable();
            MatchCollection matchs = Regex.Matches(selectList, "top\\s+\\d{1,}", RegexOptions.IgnoreCase);
            string sqlStr = string.Format("select {0} from {1} where 1=1 {2}", selectList, tableName, whereStr);
            if (!string.IsNullOrEmpty(orderExpression))
            {
                sqlStr += string.Format(" Order by {0}", orderExpression);
            }
            if (matchs.Count > 0)
            {
                DataTable dtTemp = MySqlHelper.ExecuteDataTable(sqlStr);
                rows = dtTemp.Rows.Count;
            }
            else
            {
                string sqlCount = string.Format("select count(*) from {0} where 1=1 {1} ", tableName, whereStr);
                object obj = MySqlHelper.ExecuteScalar(sqlCount);
                if (obj != null)
                {
                    rows = Convert.ToInt32(obj);
                }
            }
            dt = MySqlHelper.ExecuteDataTable(sqlStr, (pageIdex - 1) * pageSize, pageSize);
            recordCount = rows;
            return dt;
        }
        private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                for (int i = 0; i < cmdParms.Length; i++)
                {
                    MySqlParameter parameter = cmdParms[i];
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && parameter.Value == null)
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }
    }
}
